﻿CREATE DATABASE MantechHelpdesk
GO
USE MantechHelpdesk
GO
--use master
--drop database MantechHelpdesk
GO
--Bang phong ban
CREATE TABLE Departments
(
	Departments_Id INT IDENTITY(1,1),
	Departments_Name NVARCHAR(100),
	PRIMARY KEY(Departments_Id)
	
)

GO
-- bang nhan vien 
CREATE TABLE tblStaff
(
	Staff_Id INT IDENTITY(1,1),
	Staff_Name NVARCHAR(30) not null,
    Staff_Username VARCHAR(20) UNIQUE  not null,
	Staff_Password VARCHAR(100) not null,
	Staff_Sex NVARCHAR(6) null,
	Staff_age INT null,
	Staff_Phone VARCHAR(15) null,
	Staff_Address NVARCHAR(200) null,
	Staff_Email VARCHAR(50),
	Departments_Id INT null,
	Staff_Permission VARCHAR(30) not null,
	Staff_status BIT DEFAULT 1 null,
	PRIMARY KEY(Staff_Id),
	CONSTRAINT FK_Departments_STT_1 FOREIGN KEY (Departments_Id) REFERENCES Departments(Departments_Id)
)
GO
--select * from tblStaff
-- bang Employee 
CREATE TABLE tblEmployee
(
	Employee_Id INT IDENTITY(1,1),
	Employee_Name NVARCHAR(30),
    Employee_Username VARCHAR(20) UNIQUE  not null,
	Employee_Password VARCHAR(100) not null,
	Employee_Sex VARCHAR(6) null,
	Employee_age INT null,
	Employee_Phone VARCHAR(15) null,
	Employee_Address VARCHAR(100) null,
	Employee_Email VARCHAR(50) null,
	Departments_Id INT,
	Staff_status BIT DEFAULT 1,
	PRIMARY KEY(Employee_Id),
	CONSTRAINT FK_Departments_STT FOREIGN KEY (Departments_Id) REFERENCES Departments(Departments_Id)	
)
GO
-- bang phan loai khieu nai
CREATE TABLE tblComplaintsType
(
	Complaints_Type_Id INT IDENTITY(1,1),
	Complaints_Name NVARCHAR(100),
	PRIMARY KEY(Complaints_Type_Id)
)
go
 --Tinh trang
create table tblConplaintsStatus
(
	Conplaints_status_Id int identity(1,1),
	Conplaints_status_Name nvarchar(50),
	primary key (Conplaints_status_id)
)
GO
-- SELECT * FROM tblComplaints, tblComplaintsType WHERE Complaints_Id=1 AND tblComplaints.Complaints_Type_Id=tblComplaintsType.Complaints_Type_Id
-- bang khieu nai
CREATE TABLE tblComplaints
(
	Complaints_Id INT IDENTITY(1,1),
	Employee_Id INT,
	Complaints_tital NVARCHAR(50),
	DateImport DATETIME ,
	Complaints_Type_Id INT,
	Conplaints_status_Id INT DEFAULT 1, -- khi thagn nhan vien nop khieu lai mac dinh la khieu nai chua hoan thanh
	Complaints_Des NTEXT,
	WordStatus INT, -- xem cai nao da giao cho nhan vien lam hay chua.
	PRIMARY KEY (Complaints_Id),
	CONSTRAINT FK_Complaints_Type_id FOREIGN KEY (Complaints_Type_id) REFERENCES tblComplaintsType(Complaints_Type_id),
	CONSTRAINT FK_Employee_Id FOREIGN KEY (Employee_Id) REFERENCES tblEmployee(Employee_Id),
	CONSTRAINT FK_Comp_Status FOREIGN KEY (Conplaints_status_Id) REFERENCES tblConplaintsStatus(Conplaints_status_Id)
	
)

GO
-- bang uu tien

--go
-- bang phan chia cong viec
-- drop table tblword
CREATE TABLE tblWord
(
	Word_Id INT IDENTITY(1,1),
	Staff_Id INT,
	Complaints_Id INT,
	Priority_name NVARCHAR(30), -- uu tien
	DateStart DATETIME,
	PRIMARY KEY (Word_Id),
	CONSTRAINT FK_Staff_Id FOREIGN KEY (Staff_Id) REFERENCES tblStaff(Staff_Id),
	CONSTRAINT FK_Complaints_Id FOREIGN KEY (Complaints_Id) REFERENCES tblComplaints(Complaints_Id),
	
)
GO

CREATE TABLE tblWordDetail(
idWordDetail INT IDENTITY(1,1) ,
Word_Id INT,
DateClose DATETIME,
Solution_description NTEXT, -- MO TA GIAI PHAP XU LY VAN DE
PRIMARY KEY(idWordDetail),
CONSTRAINT FK_Word_Detail FOREIGN KEY(Word_Id) REFERENCES tblWord(Word_id),
)
----
GO
-- insert bang departments
INSERT INTO Departments VALUES('Educational Services')
INSERT INTO Departments VALUES('Management Services');
INSERT INTO Departments VALUES('Learning Services');
INSERT INTO Departments VALUES('Internal Systems');
INSERT INTO Departments VALUES('Human Resources');
INSERT INTO Departments VALUES('Network Suport');
INSERT INTO Departments VALUES('Hardware Suport');
INSERT INTO Departments VALUES('Software Suport');
SELECT * FROM Departments
GO
-- insert bang staff
INSERT INTO tblStaff VALUES('hoang anh tuan','admin','7815696ecbf1c96e6894b779456d330e',null,null,null,null,null,null,'admin',null)
INSERT INTO tblStaff VALUES('tring cong tuan','staff1','7815696ecbf1c96e6894b779456d330e','male',19,'0165225252','ha noi','h10t93@gmail.com',6,'staff',1)
INSERT INTO tblStaff VALUES('trang hoang anh','staff2','7815696ecbf1c96e6894b779456d330e','male',19,'0165225252','ha noi','h10t93@gmail.com',6,'staff',1)
INSERT INTO tblStaff VALUES('hoang tuan3','staff3','7815696ecbf1c96e6894b779456d330e','male',19,'0165225252','ha noi','h10t93@gmail.com',7,'staff',1)
INSERT INTO tblStaff VALUES('hoang tuan4','staff4','7815696ecbf1c96e6894b779456d330e','male',19,'0165225252','ha noi','h10t93@gmail.com',7,'staff',1)
INSERT INTO tblStaff VALUES('hoang tuan5','staff5','7815696ecbf1c96e6894b779456d330e','male',19,'0165225252','ha noi','h10t93@gmail.com',8,'staff',1)

SELECT * FROM tblStaff
GO
-- insert bang employee
INSERT INTO tblEmployee VALUES('hoang tuan1','emp1','7815696ecbf1c96e6894b779456d330e','male',19,'0165225252','ha noi','h10t93@gmail.com',1,1)
INSERT INTO tblEmployee VALUES('hoang tuan3','emp2','7815696ecbf1c96e6894b779456d330e','male',19,'0165225252','ha noi','h10t93@gmail.com',2,1)
INSERT INTO tblEmployee VALUES('hoang tuan4','emp3','7815696ecbf1c96e6894b779456d330e','male',19,'0165225252','ha noi','h10t93@gmail.com',3,1)
INSERT INTO tblEmployee VALUES('hoang tuan5','emp4','7815696ecbf1c96e6894b779456d330e','male',19,'0165225252','ha noi','h10t93@gmail.com',4,1)
INSERT INTO tblEmployee VALUES('hoang tuan5','emp5','7815696ecbf1c96e6894b779456d330e','male',19,'0165225252','ha noi','h10t93@gmail.com',5,1)
SELECT * FROM tblEmployee
GO
-- inert bang tblComplaints_Type
INSERT INTO tblComplaintsType VALUES('Network')
INSERT INTO tblComplaintsType VALUES('Hardware')
INSERT INTO tblComplaintsType VALUES('Software')
SELECT * FROM tblComplaintsType
GO
-- insert bang trang thai khieu nai
INSERT INTO tblConplaintsStatus VALUES('not completed')
INSERT INTO tblConplaintsStatus VALUES('completed')
SELECT * FROM tblConplaintsStatus
-- insert bang tblComplaints va bang trang thai
INSERT INTO tblComplaints VALUES(1,'edu',GETDATE(),1,1,'complaint abount edu',1)
INSERT INTO tblComplaints VALUES(2,'Management',GETDATE(),2,1,'complaint abount Management',1)
INSERT INTO tblComplaints VALUES(3,'Learning',GETDATE(),3,1,'complaint abount Learning',1)
INSERT INTO tblComplaints VALUES(4,'Internal Systems',GETDATE(),3,1,'complaint abount Internal Systems',1)
INSERT INTO tblComplaints VALUES(5,'Human Resources',GETDATE(),2,1,'complaint abount Human Resources',1)
SElECT * FROM tblComplaints
--SELECT * FROM tblComplaints WHERE Complaints_tital like '%ed%'
  -- thu tuc lọc khiếu nại chưa giải quyết
  -- proc fillter complaint not completed
   GO
   CREATE PROC fillterComp
   AS
   BEGIN
   SELECT tblComplaints.Complaints_Id, tblComplaints.Employee_Id, tblComplaints.Complaints_tital,
   tblComplaintsType.Complaints_Name,tblComplaints.Complaints_Des,tblComplaints.DateImport
  FROM tblComplaints INNER JOIN tblComplaintsType
  ON tblComplaintsType.Complaints_Type_Id =tblComplaints.Complaints_Type_Id 
  WHERE tblComplaints.Conplaints_status_Id =1 AND tblComplaints.WordStatus=1
   END
   GO
  -- EXEC fillterComp
   GO
   -- proc phân công
   -- drop proc divisionComp
   CREATE PROC divisionComp(@idStaff INT,@idComp INT, @priority NVARCHAR(100))
   AS
   BEGIN
   INSERT INTO tblWord VALUES( @idStaff,@idComp,@priority,GETDATE())
    UPDATE tblComplaints SET WordStatus=2 WHERE Complaints_Id=@idComp
   END
   GO
  -- EXEC divisionComp 2,2,'cao'
   --proc select staff to assignment
   -- select * from tblStaff
    CREATE PROC staffDivision
   AS
   BEGIN
   SELECT tblStaff.Staff_Id, tblStaff.Staff_Name, Departments.Departments_Name 
   FROM tblStaff INNER JOIN Departments
   ON tblStaff.Departments_Id =Departments.Departments_Id
   WHERE tblStaff.Staff_Permission='staff'
   END
   GO
 --  SELECT * FROM tblWord
  -- update tblComplaints set WordStatus=2 where tblComplaints.Complaints_Id=7
 --select * from tblComplaints
 -- select bao cao chi tiet
 GO
 -- admin xem khieu nai
 CREATE PROC procViewCompHasAssigned
 AS
 BEGIN
 SELECT tblEmployee.Employee_Id,Departments.Departments_Name, tblEmployee.Employee_Name, tblComplaints.Complaints_Id, tblComplaints.Complaints_tital
 ,tblComplaintsType.Complaints_Name,DateStart=convert(varchar(10), tblWord.DateStart, 101), tblStaff.Staff_Name, tblConplaintsStatus.Conplaints_status_Name,tblWord.Priority_name
  FROM tblComplaints,tblComplaintsType,tblStaff,tblWord,tblEmployee,Departments,tblConplaintsStatus
  WHERE tblWord.Staff_Id = tblStaff.Staff_Id
  AND tblWord.Complaints_Id=tblComplaints.Complaints_Id
  AND tblComplaints.Complaints_Type_Id=tblComplaintsType.Complaints_Type_Id
  AND  tblEmployee.Employee_Id = tblComplaints.Employee_Id
  AND tblEmployee.Departments_Id=Departments.Departments_Id
  AND tblComplaints.Conplaints_status_Id= tblConplaintsStatus.Conplaints_status_Id
  ORDER BY tblWord.DateStart
  END
  GO
  CREATE PROC procCheckStatusComp(@id INT)
  AS
  BEGIN
  SELECT tblConplaintsStatus.Conplaints_status_Name FROM tblComplaints,tblConplaintsStatus 
  WHERE tblComplaints.Conplaints_status_Id=tblConplaintsStatus.Conplaints_status_Id AND tblComplaints.Complaints_Id = @id
  END
  -- exec procViewCompHasAssigned
  GO


SELECT * FROM tblWord
SELECT * FROM tblWordDetail
GO
--pending complaints
CREATE PROC procPendingComp
AS
BEGIN
SELECT tblEmployee.Employee_Id,Departments.Departments_Name, tblEmployee.Employee_Name, tblComplaints.Complaints_Id, tblComplaints.Complaints_tital,
 tblComplaintsType.Complaints_Name,DateStart=convert(varchar(10), tblWord.DateStart, 101),tblStaff.Staff_Name
  FROM tblComplaints,tblComplaintsType,tblStaff,tblWord,tblEmployee,Departments
  WHERE tblWord.Staff_Id = tblStaff.Staff_Id
  AND tblWord.Complaints_Id=tblComplaints.Complaints_Id
  AND tblComplaints.Complaints_Type_Id=tblComplaintsType.Complaints_Type_Id
  AND  tblEmployee.Employee_Id = tblComplaints.Employee_Id
  AND tblEmployee.Departments_Id=Departments.Departments_Id
  AND tblComplaints.Conplaints_status_Id=1 AND tblComplaints.WordStatus=2
END
GO
--EXEC procPendingComp
--SELECT * FROM tblComplaints WHERE convert(varchar(10),DateImport,101)='10/19/2013'
-- report complaints detail complete by date time
CREATE PROC procCompDetailById (@idComp INT)
 AS
 BEGIN
 SELECT tblEmployee.Employee_Id,Departments.Departments_Name, tblEmployee.Employee_Name, 
 tblComplaints.Complaints_Id,tblComplaints.Complaints_tital,tblComplaints.Complaints_Des
 ,tblComplaintsType.Complaints_Name,DateStart=convert(varchar(10), tblWord.DateStart, 101),
  DateClose=convert(varchar(10),tblWordDetail.DateClose, 101), tblStaff.Staff_Name
  FROM tblComplaints,tblComplaintsType,tblStaff,tblWord,tblWordDetail,tblEmployee,Departments
  WHERE tblWord.Word_Id= tblWordDetail.Word_Id
  AND tblWord.Staff_Id = tblStaff.Staff_Id
  AND tblWord.Complaints_Id=tblComplaints.Complaints_Id
  AND tblComplaints.Complaints_Type_Id=tblComplaintsType.Complaints_Type_Id
  AND  tblEmployee.Employee_Id = tblComplaints.Employee_Id
  AND tblEmployee.Departments_Id=Departments.Departments_Id 
  AND tblComplaints.Complaints_Id=@idComp
  END
  GO
  -- exec procCompDetailById 2
  --proc report detail by rang date
 CREATE PROC procViewCompCloseByRangeDate (@dateFrom VARCHAR(20), @dateTo VARCHAR(20))
 AS
 BEGIN
 SELECT tblEmployee.Employee_Id,Departments.Departments_Name, tblEmployee.Employee_Name, 
 tblComplaints.Complaints_Id,tblComplaints.Complaints_tital,tblComplaints.Complaints_Des
 ,tblComplaintsType.Complaints_Name,DateStart=convert(varchar(10), tblWord.DateStart, 101),tblConplaintsStatus.Conplaints_status_Name,
  DateClose=convert(varchar(10),tblWordDetail.DateClose, 101), tblStaff.Staff_Name,tblWordDetail.Solution_description
  FROM tblComplaints,tblComplaintsType,tblStaff,tblWord,tblWordDetail,tblEmployee,Departments,tblConplaintsStatus
  WHERE tblWord.Word_Id= tblWordDetail.Word_Id
  AND tblWord.Staff_Id = tblStaff.Staff_Id
  AND tblWord.Complaints_Id=tblComplaints.Complaints_Id
  AND tblComplaints.Complaints_Type_Id=tblComplaintsType.Complaints_Type_Id
  AND  tblEmployee.Employee_Id = tblComplaints.Employee_Id
  AND tblEmployee.Departments_Id=Departments.Departments_Id 
  AND tblComplaints.Conplaints_status_Id= tblConplaintsStatus.Conplaints_status_Id
  AND convert(varchar(10),tblWordDetail.DateClose, 101) BETWEEN @dateFrom AND @dateTo
  END
  -- exec procViewCompCloseByRangeDate '10/28/2013','10/28/2013'
  GO
  -- proc count complaint close in date range
  CREATE PROC procTotalCompCloseInRangeDate(@dateFrom VARCHAR(20), @dateTo VARCHAR(20))
  AS
  BEGIN
  SELECT COUNT(*) AS total FROM tblComplaints,tblComplaintsType,tblStaff,tblWord,tblWordDetail,tblEmployee,Departments,tblConplaintsStatus
  WHERE tblWord.Word_Id= tblWordDetail.Word_Id
  AND tblWord.Staff_Id = tblStaff.Staff_Id
  AND tblWord.Complaints_Id=tblComplaints.Complaints_Id
  AND tblComplaints.Complaints_Type_Id=tblComplaintsType.Complaints_Type_Id
  AND  tblEmployee.Employee_Id = tblComplaints.Employee_Id
  AND tblEmployee.Departments_Id=Departments.Departments_Id 
  AND tblComplaints.Conplaints_status_Id= tblConplaintsStatus.Conplaints_status_Id
  AND convert(varchar(10),tblWordDetail.DateClose, 101) BETWEEN @dateFrom AND @dateTo
  END
  -- exec procViewCompCloseByRangeDate '10/28/2013','10/28/2013'
  GO
 
  CREATE PROC procAreHandlingCompByID(@id INT)
AS
BEGIN
SELECT tblEmployee.Employee_Id,Departments.Departments_Name, 
tblEmployee.Employee_Name, tblComplaints.Complaints_Id, tblComplaints.Complaints_tital,
tblComplaints.Complaints_Des,tblComplaintsType.Complaints_Name,
DateStart=convert(varchar(10), tblWord.DateStart, 101),tblStaff.Staff_Name, tblWord.Priority_name
  FROM tblComplaints,tblComplaintsType,tblStaff,tblWord,tblEmployee,Departments
  WHERE tblWord.Staff_Id = tblStaff.Staff_Id
  AND tblWord.Complaints_Id=tblComplaints.Complaints_Id
  AND tblComplaints.Complaints_Type_Id=tblComplaintsType.Complaints_Type_Id
  AND  tblEmployee.Employee_Id = tblComplaints.Employee_Id
  AND tblEmployee.Departments_Id=Departments.Departments_Id
  AND tblComplaints.Conplaints_status_Id=1 AND tblComplaints.WordStatus=2
  AND tblComplaints.Complaints_Id=@id
END
--exec procAreHandlingCompByID 5
GO
-- proc search complaints are handing (not complate) by id
 CREATE PROC procSearchCompNotComplateById (@id INT)
AS
BEGIN
SELECT tblEmployee.Employee_Id,Departments.Departments_Name, 
tblEmployee.Employee_Name, tblComplaints.Complaints_Id, tblComplaints.Complaints_tital,
tblComplaints.Complaints_Des,tblComplaintsType.Complaints_Name,
DateStart=convert(varchar(10), tblWord.DateStart, 101),tblStaff.Staff_Name, tblWord.Priority_name,tblConplaintsStatus.Conplaints_status_Name
  FROM tblComplaints,tblComplaintsType,tblStaff,tblWord,tblEmployee,Departments,tblConplaintsStatus
  WHERE tblWord.Staff_Id = tblStaff.Staff_Id
  AND tblComplaints.Conplaints_status_Id =tblConplaintsStatus.Conplaints_status_Id
  AND tblWord.Complaints_Id=tblComplaints.Complaints_Id
  AND tblComplaints.Complaints_Type_Id=tblComplaintsType.Complaints_Type_Id
  AND  tblEmployee.Employee_Id = tblComplaints.Employee_Id
  AND tblEmployee.Departments_Id=Departments.Departments_Id
  AND tblComplaints.Conplaints_status_Id=1  AND tblComplaints.Complaints_Id=@id
END
GO
-- select * from tblWord
--proc update complaint priority 
CREATE PROC updatePriority(@id INT, @pri VARCHAR(50))
AS
BEGIN
UPDATE tblWord SET tblWord.Priority_name=@pri WHERE tblWord.Complaints_Id=@id
END
GO
-- proc admin view complaints are handling to update priority
 CREATE PROC procViewAreHandlingComp
AS
BEGIN
SELECT tblEmployee.Employee_Id,Departments.Departments_Name, 
tblEmployee.Employee_Name, tblComplaints.Complaints_Id, tblComplaints.Complaints_tital,
tblComplaints.Complaints_Des,tblComplaintsType.Complaints_Name,
DateStart=convert(varchar(10), tblWord.DateStart, 101),tblStaff.Staff_Name, tblWord.Priority_name,tblConplaintsStatus.Conplaints_status_Name
  FROM tblComplaints,tblComplaintsType,tblStaff,tblWord,tblEmployee,Departments,tblConplaintsStatus
  WHERE tblWord.Staff_Id = tblStaff.Staff_Id
  AND tblComplaints.Conplaints_status_Id =tblConplaintsStatus.Conplaints_status_Id
  AND tblWord.Complaints_Id=tblComplaints.Complaints_Id
  AND tblComplaints.Complaints_Type_Id=tblComplaintsType.Complaints_Type_Id
  AND  tblEmployee.Employee_Id = tblComplaints.Employee_Id
  AND tblEmployee.Departments_Id=Departments.Departments_Id
  AND tblComplaints.Conplaints_status_Id=1 
END
GO
--
-- proc view complaint are handling on date range
--
 CREATE PROC procAreHandlingCompOnDateRange(@dateFrom NVARCHAR(20), @dateTo NVARCHAR(20))
AS
BEGIN
SELECT tblEmployee.Employee_Id,Departments.Departments_Name, 
tblEmployee.Employee_Name, tblComplaints.Complaints_Id, tblComplaints.Complaints_tital,
tblComplaints.Complaints_Des,tblComplaintsType.Complaints_Name,
DateStart=convert(varchar(10), tblWord.DateStart, 101),tblStaff.Staff_Name, tblWord.Priority_name,tblConplaintsStatus.Conplaints_status_Name
  FROM tblComplaints,tblComplaintsType,tblStaff,tblWord,tblEmployee,Departments,tblConplaintsStatus
  WHERE tblWord.Staff_Id = tblStaff.Staff_Id
  AND tblComplaints.Conplaints_status_Id=tblConplaintsStatus.Conplaints_status_Id
  AND tblWord.Complaints_Id=tblComplaints.Complaints_Id
  AND tblComplaints.Complaints_Type_Id=tblComplaintsType.Complaints_Type_Id
  AND  tblEmployee.Employee_Id = tblComplaints.Employee_Id
  AND tblEmployee.Departments_Id=Departments.Departments_Id
  AND tblComplaints.Conplaints_status_Id=1 AND tblComplaints.WordStatus=2
  AND convert(varchar(10), tblWord.DateStart, 101) BETWEEN @dateFrom AND @dateTo
END
-- proc count total complaints pending by day
GO
CREATE PROC procCount_Comp_Are_Handling_On_Date_Range(@dateFrom NVARCHAR(20), @dateTo NVARCHAR(20))
AS
BEGIN
SELECT COUNT(*) AS total  FROM tblComplaints,tblComplaintsType,tblStaff,tblWord,tblEmployee,Departments
  WHERE tblWord.Staff_Id = tblStaff.Staff_Id
  AND tblWord.Complaints_Id=tblComplaints.Complaints_Id
  AND tblComplaints.Complaints_Type_Id=tblComplaintsType.Complaints_Type_Id
  AND  tblEmployee.Employee_Id = tblComplaints.Employee_Id
  AND tblEmployee.Departments_Id=Departments.Departments_Id
  AND tblComplaints.Conplaints_status_Id=1 AND tblComplaints.WordStatus=2
  AND convert(varchar(10), tblWord.DateStart, 101) BETWEEN @dateFrom AND @dateTo
END
--select * from tblComplaints
GO
-- poc login eployees
CREATE PROC procLoginEmp(@user VARCHAR(100), @pass VARCHAR(100))
AS
BEGIN
SELECT * FROM tblEmployee WHERE tblEmployee.Employee_Username=@user AND tblEmployee.Employee_Password=@pass
END
GO
CREATE PROC procInfoUserLoin(@user VARCHAR(100))
AS
BEGIN
SELECT tblEmployee.Employee_Id, tblEmployee.Employee_Name FROM tblEmployee WHERE tblEmployee.Employee_Username=@user
END
GO
--proc send comlaints drop proc procEmpSendComp
--select *from tblComplaints
CREATE PROC procEmpSendComp(@empID INT, @title NVARCHAR(50),@type INT,@content NTEXT)
AS
BEGIN
INSERT INTO tblComplaints VALUES(@empID,@title,GETDATE(),@type,1,@content,1)
END
--exec procEmpSendComp 1,'asd',2,'asdsadsad'
 -- proc view complaint have been send
 GO
 CREATE PROC procViewCompSend(@id INT)
 AS
 BEGIN
 SELECT tblComplaints.Complaints_Id, tblComplaints.Complaints_tital,tblComplaintsType.Complaints_Name, tblComplaints.Complaints_Des,
  convert(varchar(10),tblComplaints.DateImport,101),tblConplaintsStatus.Conplaints_status_Name
  FROM tblComplaints, tblComplaintsType,tblConplaintsStatus
  WHERE tblComplaints.Complaints_Type_Id= tblComplaintsType.Complaints_Type_Id
  AND tblComplaints.Employee_Id=@id 
  AND tblComplaints.Conplaints_status_Id=tblConplaintsStatus.Conplaints_status_Id
  AND tblComplaints.Conplaints_status_Id=1
 END
 -- exec procViewCompSend 1
 select * from tblComplaints
 GO
 CREATE PROC procViewCompClose(@id INT)
 AS
 BEGIN
 SELECT  tblComplaints.Complaints_Id, tblComplaints.Complaints_tital,
  tblComplaintsType.Complaints_Name, tblComplaints.Complaints_Des,convert(varchar(10),tblComplaints.DateImport,101) AS dateSend,
  convert(varchar(10),tblWordDetail.DateClose,101) AS dateClose,tblStaff.Staff_Name,tblConplaintsStatus.Conplaints_status_Name
  FROM tblComplaints, tblComplaintsType, tblWord,tblStaff,tblWordDetail,tblConplaintsStatus
  WHERE tblComplaints.Complaints_Type_Id= tblComplaintsType.Complaints_Type_Id
  AND tblComplaints.Conplaints_status_Id=tblConplaintsStatus.Conplaints_status_Id
  AND tblComplaints.Employee_Id=@id
  AND tblComplaints.Complaints_Id=tblWord.Complaints_Id
  AND tblWord.Word_Id=tblWordDetail.Word_Id
  AND tblWord.Staff_Id=tblStaff.Staff_Id
  AND tblComplaints.Conplaints_status_Id=2
 END
 -- exec  procViewCompClose 1,1
 GO
 -- proc view comp close by date
 CREATE PROC procViewCompCloseByDate(@id INT, @dateFrom VARCHAR(20), @dateTo VARCHAR(20))
 AS
 BEGIN
 SELECT tblComplaints.Complaints_Id, tblComplaints.Complaints_tital,
  tblComplaintsType.Complaints_Name, tblComplaints.Complaints_Des,convert(varchar(10),tblComplaints.DateImport,101) AS dateSend,
  convert(varchar(10),tblWordDetail.DateClose,101) AS dateClose,tblStaff.Staff_Name,tblConplaintsStatus.Conplaints_status_Name
  FROM tblComplaints, tblComplaintsType, tblWord,tblStaff,tblWordDetail,tblConplaintsStatus
  WHERE tblComplaints.Complaints_Type_Id= tblComplaintsType.Complaints_Type_Id
  AND tblComplaints.Employee_Id=@id
  AND tblComplaints.Complaints_Id=tblWord.Complaints_Id
  AND tblWord.Word_Id=tblWordDetail.Word_Id
  AND tblWord.Staff_Id=tblStaff.Staff_Id
  AND tblComplaints.Conplaints_status_Id=tblConplaintsStatus.Conplaints_status_Id
  AND tblComplaints.Conplaints_status_Id=2
  AND  convert(varchar(10),tblWordDetail.DateClose,101)BETWEEN @dateFrom AND @dateTo
 END
 -- exec procViewCompCloseByDate 1, '10/29/2013', '10/29/2013'
 GO
  CREATE PROC procViewCompClosedDetail(@id INT)
 AS
 BEGIN
 SELECT  tblComplaints.Complaints_Id, tblComplaints.Complaints_tital,
  tblComplaintsType.Complaints_Name, tblComplaints.Complaints_Des,convert(varchar(10),tblComplaints.DateImport,101) AS dateSend,
  convert(varchar(10),tblWordDetail.DateClose,101) AS dateClose,tblStaff.Staff_Name,tblConplaintsStatus.Conplaints_status_Name
  ,tblWordDetail.Solution_description
  FROM tblComplaints, tblComplaintsType, tblWord,tblStaff,tblWordDetail,tblConplaintsStatus
  WHERE tblComplaints.Complaints_Type_Id= tblComplaintsType.Complaints_Type_Id
  AND tblComplaints.Conplaints_status_Id=tblConplaintsStatus.Conplaints_status_Id
  AND tblComplaints.Complaints_Id=tblWord.Complaints_Id
  AND tblWord.Word_Id=tblWordDetail.Word_Id
  AND tblWord.Staff_Id=tblStaff.Staff_Id
  AND tblComplaints.Conplaints_status_Id=2
  AND tblComplaints.Complaints_Id=@id
 END
 -- proc staff view complaint must resolve
 GO
 CREATE PROC procStaffviewComp(@id INT)
 AS
 BEGIN
 SELECT tblWord.Word_Id, tblComplaints.Complaints_Id,tblComplaints.Complaints_tital,
  tblComplaints.Complaints_Des, tblWord.Priority_name, convert(varchar(10),tblWord.DateStart,101)
  FROM tblWord, tblComplaints
  WHERE tblComplaints.Complaints_Id=tblWord.Complaints_Id
  AND tblWord.Staff_Id=@id
  AND tblComplaints.Conplaints_status_Id=1
   AND tblComplaints.WordStatus=2
 END
 -- proc technician view complains by date range
 GO
 CREATE PROC procStaffviewCompByDate(@id INT, @dateFrom VARCHAR(20), @dateTo VARCHAR(20))
 AS
 BEGIN
 SELECT tblWord.Word_Id, tblComplaints.Complaints_Id,tblComplaints.Complaints_tital,
  tblComplaints.Complaints_Des, tblWord.Priority_name, convert(varchar(10),tblWord.DateStart,101)
  FROM tblWord, tblComplaints
  WHERE tblComplaints.Complaints_Id=tblWord.Complaints_Id
  AND tblWord.Staff_Id=@id
  AND convert(varchar(10),tblWord.DateStart,101)BETWEEN @dateFrom AND @dateTo
  AND tblComplaints.Conplaints_status_Id=1
   AND tblComplaints.WordStatus=2
 END
 GO
 -- proc technician view complaints by priority
 CREATE PROC procStaffviewCompByPri(@id INT, @pri VARCHAR(50))
 AS
 BEGIN
 SELECT tblWord.Word_Id, tblComplaints.Complaints_Id,tblComplaints.Complaints_tital,
  tblComplaints.Complaints_Des, tblWord.Priority_name, convert(varchar(10),tblWord.DateStart,101)
  FROM tblWord, tblComplaints
  WHERE tblComplaints.Complaints_Id=tblWord.Complaints_Id
  AND tblWord.Staff_Id=@id
  AND tblWord.Priority_name=@pri
  AND tblComplaints.Conplaints_status_Id=1
   AND tblComplaints.WordStatus=2
 END
 --exec drop proc procStaffviewCompByDate 2, '10/1/2013', '11/11/2013'
 --exec procStaffviewComp 2
 --select * from tblWordDetail
 --select * from tblWord
  -- select * from tblComplaints
  -- select * from tblEmployee
 GO
 CREATE PROC procInserWordDetail(@idWord INT,@solutin NTEXT, @idComp INT)
 AS
 BEGIN
 INSERT INTO tblWordDetail VALUES(@idWord,GETDATE(),@solutin)
 UPDATE tblComplaints SET tblComplaints.Conplaints_status_Id=2 WHERE  tblComplaints.Complaints_Id=@idComp
 END
 -- proc create account technician
 GO
 CREATE PROC procCreateAccountTechnician
 (@name NVARCHAR(100),
  @user VARCHAR(20),
  @pass VARCHAR(100),
  @sex NVARCHAR(6),
  @age INT,
  @phone VARCHAR(20),
  @address NVARCHAR(200),
  @email VARCHAR(50),
  @departmentsID INT
 )
 AS
 BEGIN
 INSERT INTO tblStaff VALUES(@name,@user,@pass,@sex,@age,@phone,@address,@email,@departmentsID,'staff',1)
 END 
 -- exec procCreateAccountTechnician 'hoangtun','staff11','asd','nam',12,'123','asd','asd@ad',2

 GO
 CREATE PROC procViewAccountStaff
 AS
 BEGIN
 SELECT tblStaff.Staff_Name, tblStaff.Staff_Username,
 tblStaff.Staff_Sex,tblStaff.Staff_age, tblStaff.Staff_Phone,
 tblStaff.Staff_Address,tblStaff.Staff_Email, Departments.Departments_Name
 FROM tblStaff, Departments WHERE tblStaff.Departments_Id=Departments.Departments_Id
 AND tblStaff.Staff_Permission='staff' AND tblStaff.Staff_status=1
 END
 GO
 --
 --proc delete account staff
 --
  CREATE PROC procDeleteAccountStaff (@user VARCHAR(20))
 AS
 BEGIN
 UPDATE tblStaff SET Staff_status=0 WHERE tblStaff.Staff_Username=@user
 END

 GO
 --
 -- proc view staff by name
 --
 CREATE PROC procViewAccountStaffByName(@name NVARCHAR(100))
 AS
 BEGIN
 SELECT tblStaff.Staff_Name, tblStaff.Staff_Username,
 tblStaff.Staff_Sex,tblStaff.Staff_age, tblStaff.Staff_Phone,
 tblStaff.Staff_Address,tblStaff.Staff_Email, Departments.Departments_Name
 FROM tblStaff, Departments WHERE tblStaff.Departments_Id=Departments.Departments_Id
 AND tblStaff.Staff_Permission='staff' AND tblStaff.Staff_status=1
 AND tblStaff.Staff_Name=@name
 END
 GO
 --
 -- proc create user
 --
 CREATE PROC procCreateAccountUser
 (@name NVARCHAR(100),
  @user VARCHAR(20),
  @pass VARCHAR(100),
  @sex NVARCHAR(6),
  @age INT,
  @phone VARCHAR(20),
  @address NVARCHAR(200),
  @email VARCHAR(50),
  @departmentsID INT
 )
 AS
 BEGIN
 INSERT INTO tblEmployee VALUES(@name,@user,@pass,@sex,@age,@phone,@address,@email,@departmentsID,1)
 END 
 GO
 --
 --proc delete account staff
 --
  CREATE PROC procDeleteAccountEmp (@user VARCHAR(20))
 AS
 BEGIN
 UPDATE tblEmployee SET Staff_status=0 WHERE Employee_Username=@user
 END

 GO

 CREATE PROC procViewAccountEmpByName(@name NVARCHAR(100))
 AS
 BEGIN
 SELECT tblEmployee.Employee_Name, tblEmployee.Employee_Username,
 tblEmployee.Employee_Sex,tblEmployee.Employee_age, tblEmployee.Employee_Phone,
 tblEmployee.Employee_Address,tblEmployee.Employee_Email, Departments.Departments_Name
 FROM tblEmployee, Departments WHERE tblEmployee.Departments_Id=Departments.Departments_Id
 AND tblEmployee.Employee_Name=@name
 AND tblEmployee.Staff_status=1
 END
 GO
 --
 -- proc view emp
 --
  CREATE PROC procViewAccountEmp
 AS
 BEGIN
SELECT tblEmployee.Employee_Name, tblEmployee.Employee_Username,
 tblEmployee.Employee_Sex,tblEmployee.Employee_age, tblEmployee.Employee_Phone,
 tblEmployee.Employee_Address,tblEmployee.Employee_Email, Departments.Departments_Name
 FROM tblEmployee, Departments WHERE tblEmployee.Departments_Id=Departments.Departments_Id
 AND tblEmployee.Staff_status=1
 END

 GO
 --
 -- proc count complaint close by day
 --
 CREATE PROC procCountCompCloseByDay (@day VARCHAR(20))
 AS
 BEGIN
 select COUNT(*) FROM tblWordDetail WHERE convert(varchar(10),tblWordDetail.DateClose,101)=@day
 END	
 GO
 --
 -- proc view info loin
 --
CREATE PROC  procViewinfoLogin(@user VARCHAR(50))
AS
BEGIN
SELECT tblStaff.Staff_Id, tblStaff.Staff_Name FROM tblStaff WHERE tblStaff.Staff_Username =@user
END
GO
--
-- proc view type of complaint request most
--
CREATE PROC procIDCompTypeMostRequest (@dateFrom VARCHAR(50), @dateTo VARCHAR(50))
AS
BEGIN
SELECT TOP 1 Complaints_Type_Id,COUNT(Complaints_Type_Id) AS total
FROM tblComplaints WHERE  convert(varchar(10),tblComplaints.DateImport,101) BETWEEN @dateFrom AND @dateTo
 group by tblComplaints.Complaints_Type_Id ORDER BY total DESC
END

GO
--
-- proc list complaints request most
--

CREATE PROC procListComplaintsRequestMost(@type int,@dateFrom VARCHAR(50),@dateTo VARCHAR(50))
AS
BEGIN
SELECT tblComplaints.Complaints_Id, tblComplaints.Complaints_tital, tblComplaintsType.Complaints_Name,
tblComplaints.Complaints_Des, tblComplaints.DateImport, tblConplaintsStatus.Conplaints_status_Name, tblEmployee.Employee_Id
,tblEmployee.Employee_Name
FROM tblComplaints, tblComplaintsType, tblConplaintsStatus, tblEmployee
WHERE tblComplaints.Complaints_Type_Id=tblComplaintsType.Complaints_Type_Id
AND tblComplaints.Conplaints_status_Id= tblConplaintsStatus.Conplaints_status_Id
AND tblComplaints.Employee_Id=tblEmployee.Employee_Id
AND tblComplaints.Complaints_Type_Id=@type
AND convert(varchar(10),tblComplaints.DateImport,101)BETWEEN @dateFrom AND @dateTo
END
GO
--
-- proc Employee change password
--
CREATE PROC procChangePassEmp(@id INT, @pass VARCHAR(100), @passOlder VARCHAR(100))
AS
BEGIN
UPDATE tblEmployee SET Employee_Password=@pass WHERE Employee_Id=@id AND Employee_Password=@passOlder
END
GO
--
-- proc technician change password
--
CREATE PROC procChangePassStaff(@id INT, @pass VARCHAR(100), @passOlder VARCHAR(100))
AS
BEGIN
UPDATE tblStaff SET Staff_Password=@pass WHERE Staff_Id=@id AND Staff_Password=@passOlder
END
